-- @owner: lwx1120110
-- @date: 2022/02/11
-- @testpoint: 临时表组合索引，关联普通表，delete关联查询使用全局临时表，condition使用in条件，部分测试点合理报错

--step1:建表;expect:成功
drop table if exists table_idx_in_temp_0030;
create temporary table table_idx_in_temp_0030(
staff_id int not null,
is_graduate bool ,
course_name varchar(500) not null,
higest_degree varchar2(500) not null,
course_start_date date,
course_end_date date,
exam_date date,
score int) with (storage_type=ustore) on commit preserve rows;

--step2:创建索引;expect:创建索引成功
drop index if exists idx_in_006_001;
create index idx_in_006_001 on table_idx_in_temp_0030(staff_id,course_name,course_start_date);

--step3:创建索引;expect:创建索引成功
drop index if exists idx_in_006_002;
create index idx_in_006_002 on table_idx_in_temp_0030(staff_id,is_graduate,course_start_date asc,score desc) ;

--step4:创建sequence;expect:成功
drop sequence if exists v_seq;
create sequence v_seq increment by 2 start with 1;

--step5:插入数据;expect:成功
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','english','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','painting','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','drawing','doctor',null,'2018-06-27 12:00:00','2018-06-29 12:00:00',95);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','grammer','master','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','system','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','language','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','grammer','doctor','2017-06-15 12:00:00',null,'2017-06-25 12:00:00',90);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','chinese','scholar','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','math','doctor','2018-06-25 12:00:00','2018-06-27 12:00:00','2018-06-29 12:00:00',95);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','sql majorization','doctor','2017-06-15 12:00:00','2017-06-20 12:00:00','2017-06-25 12:00:00',90);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','big data','master','2018-06-15 12:00:00','2018-06-20 12:00:00','2018-06-25 12:00:00',null);
insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','performance turning','scholar','2018-06-25 12:00:00','2018-06-27 12:00:00',null,95);


begin
    for i in 1..300 loop
    insert into table_idx_in_temp_0030 values(v_seq.nextval,'true','english','master',add_months('2018-06-15 12:00:00',i),
    '2018-06-29 12:00:00','2017-06-25 12:00:00',83+mod(i,16));
    end loop;
end;
/


begin
    for i in 1..400 loop
    insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','performance turning','scholar','2018-06-15 12:00:00',
    add_months('2018-01-15 12:00:00',i),add_months('2017-06-25 12:00:00',mod(i,11)),86+mod(i,14));
    end loop;
end;
/


begin
    for i in 1..500 loop
    insert into table_idx_in_temp_0030 values(v_seq.nextval,'false','language','doctor',add_months('2017-06-15 12:00:00',i-5),
    add_months('2018-01-16 12:25:00',i),add_months('2017-06-25 12:00:00',mod(i,5)),mod(65+i,100));
    end loop;
end;
/

--step6:使用exists子查询;expect:查询数据成功
select staff_id,course_name,score from table_idx_in_temp_0030 where exists (
    select staff_id from table_idx_in_partition_004 where higest_degree= 'master'
    and score in (86,97,98,234,0,83) and course_start_date
    in ('2018-06-15 12:00:00','2018-06-25 12:00:00','2019-06-15 12:00:00'))
order by 1,2,3 limit 10 ;

--step7:关联普通表，查询使用in条件;expect:查询数据为空
select t1.* from table_idx_in_temp_0030 t2 join table_idx_in_partition_004 t1
on t1.staff_id =t2.staff_id and t1.course_name in( 'language','chinese','sql majorization','performance turning' )
and t1.course_start_date in ('2018-06-15 12:00:00','2018-06-15 12:00:00','2017-09-15 12:00:00')
and t2.is_graduate in ('false','true') and t2.staff_id in (1,3,5,7,37,235,248,369,397,457,699,1234,1023,2003,1975,455,673) limit 10;

--step8:delete关联查询使用全局临时表，condition使用in条件-不支持;expect:合理报错
delete  from table_idx_in_partition_004  join table_idx_in_temp_0030
on table_idx_in_temp_0030.higest_degree =table_idx_in_partition_004.higest_degree
and table_idx_in_temp_0030.course_name
in ('english','system','performance turning','hidtory','drawing','system','aotumatic','sql majorization')
and table_idx_in_temp_0030.course_start_date
in ('2018-06-15 12:00:00','2018-06-25 12:00:00','2019-06-15 12:00:00','2017-09-15 12:00:00','2017-06-15 12:00:00');

--step9:清理环境;expect:成功
drop index if exists idx_in_006_001;
drop index if exists idx_in_006_002;
drop sequence if exists v_seq;
drop table if exists table_idx_in_temp_0030;